
CREATE  trigger tt_billlay_ai
on dbo.tt_billlay
for insert
as
begin  
  set nocount on
  declare @depotid integer, @productid integer, @layid integer, @quantity decimal(18,4),
    @assqty decimal(18,4), @old_beginqty decimal(18,4), @old_beginassqty decimal(18,4)
  declare ai_cursor cursor local forward_only static optimistic for
    select depotid, productid, layid, bquantity, assqty from tt_billlay
  open ai_cursor
  fetch next from ai_cursor into @depotid, @productid, @layid, @quantity, @assqty
  while (@@fetch_status = 0)
  begin
    if (exists(select 1 from tt_booklay where depotid = @depotid and productid = @productid and layid = @layid))
    begin
      set @old_beginqty = 0
      set @old_beginassqty = 0
      
      select @old_beginqty = beginqty, @old_beginassqty = beginassqty from tt_booklay
        where depotid = @depotid and productid = @productid and layid = @layid  
      
      update tt_booklay
      set beginqty = @quantity, beginassqty = @assqty, 
        quantity = quantity - @old_beginqty + @quantity,
        assqty = assqty - @old_beginassqty + @assqty,
        layid = @layid, productid = @productid, depotid = @depotid      
      where layid = @layid and productid = @productid and depotid = @depotid  
    end
    else
      insert into tt_booklay(layid, depotid, productid, quantity, beginqty, assqty, beginassqty)
      values(@layid, @depotid, @productid, @quantity, @quantity, @assqty, @assqty)  
    fetch next from ai_cursor into @depotid, @productid, @layid, @quantity, @assqty
  end
  close ai_cursor
  deallocate ai_cursor
end


